package com.action.proceso6;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.bean.proceso6.OrdenDeServicioItemOrden;
import com.conexion.DbConnection;
import com.mysql.jdbc.Connection;

public class OrdenDeServicioItemOrdenAction {

	private final String TABLA = "ordendeservicio_itemorden";

	// metodo para insertar OrdenDeServicioItemOrdenAction

	public int Insertar(OrdenDeServicioItemOrden obj) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = DbConnection.getConnection();

			String sql = "INSERT INTO " + TABLA + " VALUES(?, ?)";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, obj.getOrdendeservicio_id());
			pstm.setString(2, obj.getItemorden_id());

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioItemOrdenAction.class.getName())
					.log(Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	// otro insertar

	public int insertar(ArrayList<OrdenDeServicioItemOrden> lista) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = DbConnection.getConnection();

			for (OrdenDeServicioItemOrden obj : lista) {
				String sql = "INSERT INTO " + TABLA + " VALUES(?, ?)";
				pstm = conn.prepareStatement(sql);
				pstm.setString(1, obj.getItemorden_id());
				pstm.setString(2, obj.getOrdendeservicio_id());
				

				pstm.executeUpdate();
			}
			return 1;

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioItemOrdenAction.class.getName())
					.log(Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	// actualizar

	public int actualizar(ArrayList<OrdenDeServicioItemOrden> lista) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = DbConnection.getConnection();
			String sql = "Delete from " + TABLA + " where ordendeservicio_id='"
					+ lista.get(0).getOrdendeservicio_id()
					+ " and itemorden_id not in ('";
			for (int i = 0; i < lista.size(); i++) {
				if (i < lista.size() - 1)
					sql += lista.get(i).getItemorden_id() + "', ";
				else
					sql += lista.get(i).getItemorden_id() + "')";
			}
			pstm = conn.prepareStatement(sql);
			pstm.executeUpdate();

			sql = "Insert into " + TABLA + " values";
			for (int i = 0; i < lista.size(); i++) {
				if (i < lista.size() - 1)
					sql += "('" + lista.get(i).getOrdendeservicio_id() + "', '"
							+ lista.get(i).getItemorden_id() + "'), ";
				else
					sql += "('" + lista.get(i).getOrdendeservicio_id() + "', '"
							+ lista.get(i).getItemorden_id() + "')";
			}
			sql += " where itemorden_id not in (Select itemorden_id from "
					+ TABLA + ")";
			pstm = conn.prepareStatement(sql);
			pstm.executeUpdate();

			return 1;

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioItemOrdenAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	// meto para listar orden de servicio item

	public ArrayList<OrdenDeServicioItemOrden> listar() {

		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<OrdenDeServicioItemOrden> osItem = new ArrayList<OrdenDeServicioItemOrden>();

		try {
			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA;
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				osItem.add(new OrdenDeServicioItemOrden(rs.getString(1), rs
						.getString(2)));
			}

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioItemOrdenAction.class.getName())
					.log(Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return osItem;
	}

	// metodo para buscar el Orden Sevicio Item

	public OrdenDeServicioItemOrden buscar(String ordendeservicio_id,
			String itemorden_id) {

		Connection conn = null;
		PreparedStatement pstm = null;
		OrdenDeServicioItemOrden osi = null;
		try {

			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA
					+ " where ordendeservicio_id=? and itemorden_id=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, ordendeservicio_id);
			pstm.setString(2, itemorden_id);

			ResultSet rs = pstm.executeQuery();

			if (rs.next())
				osi = new OrdenDeServicioItemOrden(rs.getString(1),
						rs.getString(2));

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioItemOrdenAction.class.getName())
					.log(Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return osi;
	}

	// metodo para eliminar el orden servicio item

	public int eliminar(String ordendeservicio_id, String itemorden_id) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = DbConnection.getConnection();

			String sql = "delete from " + TABLA
					+ " where  ordendeservicio_id=? and itemorden_id=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, ordendeservicio_id);
			pstm.setString(2, itemorden_id);

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioItemOrdenAction.class.getName())
					.log(Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	public int eliminar(String ordendeservicio_id) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = DbConnection.getConnection();

			String sql = "delete from ordendeservicio_itemorden where ordendeservicio_id=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, ordendeservicio_id);

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioItemOrdenAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	public ArrayList<OrdenDeServicioItemOrden> listar(String codigo) {
		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<OrdenDeServicioItemOrden> lista = new ArrayList<OrdenDeServicioItemOrden>();

		try {
			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA
					+ " where ordendeservicio_id=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, codigo);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				lista.add(new OrdenDeServicioItemOrden(rs.getString(1), rs
						.getString(2)));
			}

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeServicioItemOrdenAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lista;
	}

}
